package com.larry.sql;

import java.math.BigDecimal;

import com.pegatron.ers.util.DateUtil;
import com.pegatron.ers.util.StrUtil;

public class SqlTest {
	
	private static String startDate = "2013/02/01";

	private static String endDate = "2013/02/10";

	public static void main(String[] args) {
		StringBuffer buf = new StringBuffer();
		String fixMessage = null;
		String startDate = null;
		String endDate = null;
		BigDecimal rmaSite= new BigDecimal("31");
		BigDecimal orderNo= new BigDecimal("7490");
		BigDecimal itemNo = new BigDecimal("10119724");
		buf.append("	SELECT");
		buf.append("		CASE");
		buf.append("			WHEN (a.product_type = 'JNM') THEN 'NB'");
		buf.append("			WHEN (a.product_type = 'NM') THEN 'NB'");
		buf.append("			WHEN (a.product_type = 'M') THEN 'DT'");
		buf.append("			ELSE a.product_type");
		buf.append("		END product_type,");
		buf.append("		(SELECT x.area_code FROM pf_cpy_profile x WHERE x.id = b.customer) customer_name,");
		buf.append("		b.order_nr,");
		buf.append("		TO_CHAR (NVL((SELECT MAX (x.ct_date) FROM ers_order_track x WHERE x.order_no = b.order_no AND x.rma_site = b.rma_site AND x.status IN ('RECEIVE', 'UNPACK')), (SELECT MAX (x.ct_date) FROM ers_order_track x WHERE x.order_no = b.order_no AND x.rma_site = b.rma_site AND x.status IN ('KEYIN'))), 'YYYY-MM-DD') receive_date,");
		buf.append("		a.serial_nr new_serial_nr,");
		buf.append("		c.org_pcba,");
		buf.append("		c.org_bios,");
		buf.append("		a.customer_memo,");
		buf.append("		ers_util_p.getfirstitemserialnr (a.item_no, a.rma_site) serial_nr,");
		buf.append("		to_char((select ers_util_p.getitemkeyinsubmitdate(a.item_no, a.rma_site) from dual), 'YYYY-MM-DD') keyin_date,");
		buf.append("		to_char((select ers_util_p.getitemcloseddate(a.item_no, a.rma_site) from dual), 'YYYY-MM-DD') closed_date,");
		buf.append("		CASE");
		buf.append("			WHEN d.station = 'SCRAP' THEN e.scrap_code");
		buf.append("			WHEN d.problem_code = 'X00001' THEN 'M0FZ00'");
		buf.append("			WHEN f.final_code = 'NTF' THEN 'M0Z000'");
		buf.append("			WHEN d.process_no IS NULL THEN e.scrap_code");
		buf.append("			ELSE d.problem_code");
		buf.append("		END problem_code,");
		buf.append("		CASE");
		buf.append("			WHEN d.station = 'SCRAP'");
		buf.append("			THEN (SELECT x.description FROM ers_cf_def_code x WHERE x.code_group = 'A3' AND x.code = e.scrap_code AND x.dt_id IS NULL AND ROWNUM = 1)");
		buf.append("			WHEN d.problem_code = 'X00001'");
		buf.append("			THEN (SELECT x.problem_name FROM ers_cf_problem x WHERE x.problem_id = 'M0FZ00' AND x.dt_id IS NULL AND ROWNUM = 1)");
		buf.append("			WHEN f.final_code = 'NTF'");
		buf.append("			THEN (SELECT x.problem_name FROM ers_cf_problem x WHERE x.problem_id = 'M0Z000' AND x.dt_id IS NULL AND ROWNUM = 1)");
		buf.append("			WHEN d.process_no IS NULL");
		buf.append("			THEN (SELECT x.description FROM ers_cf_def_code x WHERE x.code_group = 'A3' AND x.code = e.scrap_code AND x.dt_id IS NULL AND ROWNUM = 1)");
		buf.append("			ELSE ers_util_p.getproblemdesc (a.item_no, a.rma_site, d.code_group, d.problem_code)");
		buf.append("		END problem_desc,");
		buf.append("		CASE");
		buf.append("			WHEN d.reason_code IS NOT NULL THEN d.reason_code");
		buf.append("			WHEN f.final_code = 'NTF' THEN 'N01'");
		buf.append("			ELSE d.reason_code");
		buf.append("		END reason_code,");
		buf.append("		CASE");
		buf.append("			WHEN d.reason_code IS NOT NULL");
		buf.append("			THEN ers_util_p.getreasondesc (d.reason_code)");
		buf.append("			WHEN f.final_code = 'NTF'");
		buf.append("			THEN ers_util_p.getreasondesc ('N01')");
		buf.append("			ELSE ers_util_p.getreasondesc (d.reason_code)");
		buf.append("		END reason_desc,");
		buf.append("		g.org_part_nr,");
		buf.append("		ers_util_p.getpartdesc (g.org_part_nr) org_part_desc,");
		buf.append("		(SELECT x.org_pn FROM ers_item_exchange x WHERE x.dt_id IS NULL AND x.item_no = d.item_no AND x.rma_site = d.rma_site AND x.process_no = d.process_no");
		buf.append("			AND x.seq_no = (SELECT MAX (y.seq_no) FROM ers_item_exchange y WHERE y.dt_id IS NULL AND x.item_no = y.item_no AND x.rma_site = y.rma_site AND x.process_no = y.process_no)) exchange_org_part_nr,");
		buf.append("		(SELECT ers_util_p.getpartdesc (x.org_pn) FROM ers_item_exchange x WHERE x.dt_id IS NULL AND x.item_no = d.item_no AND x.rma_site = d.rma_site AND x.process_no = d.process_no");
		buf.append("			AND x.seq_no = (SELECT MAX (y.seq_no) FROM ers_item_exchange y WHERE y.dt_id IS NULL AND x.item_no = y.item_no AND x.rma_site = y.rma_site AND x.process_no = y.process_no)) exchange_org_part_desc,");
		buf.append("		g.loc,");
		buf.append("		g.repair_type,");
		buf.append("		(SELECT x.description FROM ers_cf_def_code x WHERE x.code_group = 'A10' AND x.rma_site IN (a.rma_site, 0) AND x.code = g.repair_type AND ROWNUM = 1) repair_type_desc,");
		buf.append("		CASE");
		buf.append("			WHEN f.final_code IN ('CID', 'CID-credit', 'CID-no repair and return', 'CID-Repaired OK-L1', 'CID-FW/SW Solved', 'VID-credit', 'VID-Repaired OK-L1', 'FW/SW/Setting Solved', 'NTF')");
		buf.append("			THEN '1'");
		buf.append("			WHEN f.final_code IN ('CID-Repaired OK-L2', 'VID-Repaired OK-L2')");
		buf.append("			THEN '2'");
		buf.append("			WHEN f.final_code IN ('VID-Repaired OK-L3', 'CID-Repaired OK-L3', 'CID-Repaired OK') AND (d.reason_code = 'R07' OR d.action = 'REHEAT')");
		buf.append("			THEN '2.5'");
		buf.append("			WHEN f.final_code IN ('VID-Repaired OK-L3', 'CID-Repaired OK-L3', 'CID-Repaired OK') AND g.repair_type IN ('R001', 'BGA')");
		buf.append("			THEN '3'");
		buf.append("			WHEN f.final_code IN ('VID-Repaired OK-L3', 'CID-Repaired OK-L3', 'CID-Repaired OK')");
		buf.append("			THEN '4'");
		buf.append("			WHEN f.final_code IN ('VID-Repaired OK-L4', 'CID-Repaired OK-L4') AND g.repair_type IN ('R001', 'BGA')");
		buf.append("			THEN '4'");
		buf.append("			WHEN f.final_code IN ('VID-Repaired OK-L4', 'CID-Repaired OK-L4')");
		buf.append("			THEN '3'");
		buf.append("			WHEN f.final_code IN ('VID-scrapped', 'VID-exchanged', 'VID-stock in BAD Stock', 'CID-scrapped', 'CID-exchanged', 'ECR', 'EVFF', 'VID', 'VID-exchanged (Shortage)') AND d.reason_code IS NULL");
		buf.append("			THEN '1'");
		buf.append("			WHEN f.final_code IN ('VID-scrapped', 'VID-exchanged', 'VID-stock in BAD Stock', 'CID-scrapped', 'CID-exchanged', 'ECR', 'EVFF', 'VID', 'VID-exchanged (Shortage)') AND g.repair_type IN ('R001', 'BGA')");
		buf.append("			THEN '4'");
		buf.append("			WHEN f.final_code IN ('VID-scrapped', 'VID-exchanged', 'VID-stock in BAD Stock', 'CID-scrapped', 'CID-exchanged', 'ECR', 'EVFF', 'VID', 'VID-exchanged (Shortage)')");
		buf.append("			THEN '3'");
		buf.append("			ELSE 'UNKOWN Level'");
		buf.append("		END repair_level,");
		buf.append("		b.order_no,");
		buf.append("		a.item_no,");
		buf.append("		a.order_keyin_no line_no,");
		buf.append("		d.process_no,");
		buf.append("		g.seq_no,");
		buf.append("		b.order_type,");
		buf.append("		a.product_type pega_product_type,");
		buf.append("		d.module,");
		buf.append("		d.station,");
		buf.append("		d.action,");
		buf.append("		a.part_nr,");
		buf.append("		ers_util_p.getcustomername (b.customer) pega_profile_name,");
		buf.append("		f.final_code,");
		buf.append("		a.rma_site");
		buf.append("	FROM");
		buf.append("		ers_item a,");
		buf.append("		ers_order b,");
		buf.append("		ers_item_info c,");
		buf.append("		(SELECT * FROM ers_process x WHERE x.dt_id IS NULL AND x.module != 'TEST') d,");
		buf.append("		ers_item_hist e,");
		buf.append("		ers_item_summary f,");
		buf.append("		ers_repair g");
		buf.append("	WHERE");
		buf.append("		a.dt_id IS NULL");
		buf.append("		AND b.dt_id IS NULL");
		buf.append("		AND a.order_no = b.order_no");
		buf.append("		AND a.rma_site = b.rma_site");
		buf.append("		AND a.item_no = c.item_no");
		buf.append("		AND a.rma_site = c.rma_site");
		buf.append("		AND a.item_no = e.item_no");
		buf.append("		AND a.rma_site = e.rma_site");
		buf.append("		AND a.item_no = f.item_no");
		buf.append("		AND a.rma_site = f.rma_site");
		buf.append("		AND a.item_no = d.item_no(+)");
		buf.append("		AND a.rma_site = d.rma_site(+)");
		buf.append("		AND d.item_no = g.item_no(+)");
		buf.append("		AND d.rma_site = g.rma_site(+)");
		buf.append("		AND d.process_no = g.process_no(+)");
		buf.append("		AND a.status = 'CLOSED'");
		buf.append("		AND a.rma_site IN (52, 58)");
		buf.append("		AND EXISTS (SELECT 1 FROM ers_pf_profile x WHERE x.profile_id = b.customer AND x.customer_type IN ('PEACER', 'PE_ACER'))");
		if (!StrUtil.isEmpty(itemNo)) {
			buf.append("	and b.item_no=" + itemNo);
		}
		System.out.println(buf.toString());
	}
}
